﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace SRL_Statistics.Services
{
    public class DBAccessor : IDisposable
    {
        private SqlConnection connection;

        private const string statsTableName = "Stats";

        private const string adminTableName = "Administration";

        public DBAccessor()
        {
            try
            {
                connection = new SqlConnection("user id=SrlAdmin;" +
                                       "password=VillavuSecureStats;server=localhost\\sqlexpress;database=Stats;" +
                                       "Trusted_Connection=true;Asynchronous Processing=True;" +
                                       "connection timeout=7200;MultipleActiveResultSets=true");
                connection.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
        }

        public bool RegisterScript(string scriptName, string password)
        {
            SqlCommand command = new SqlCommand("SELECT SCRIPTNAME FROM " + adminTableName + " WHERE ScriptName = @scriptName", connection);
            command.Parameters.AddWithValue("@scriptName", scriptName);

            bool errored = false;
            try
            {
                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read() && (reader.GetSqlString(0).Value != null))//We don't want to add a script that already exists, now do we?
                {
                    errored = true;
                }
                reader.Close();
            }
            catch (InvalidOperationException)
            {
                errored = true;
            }
            if (errored)
            {
                return false;
            }

            SqlCommand insertCommand = new SqlCommand("INSERT INTO " + adminTableName + " (ScriptName, Password) VALUES (@scriptName,@password)", connection);
            insertCommand.Parameters.AddWithValue("@scriptName", scriptName);
            insertCommand.Parameters.AddWithValue("@password", password);
            bool successfulRegistration = insertCommand.ExecuteNonQuery() == 1;
            return successfulRegistration;
        }
        public bool RegisterScript(string scriptName, string password, List<String> variables)
        {
            SqlCommand command = new SqlCommand("SELECT SCRIPTNAME FROM " + adminTableName + " WHERE ScriptName = @scriptName AND Password = @password", connection);
            command.Parameters.AddWithValue("@scriptName", scriptName);
            command.Parameters.AddWithValue("@password", password);

            SqlDataReader reader = command.ExecuteReader();
            try
            {
                if(!(reader.Read() && reader.GetSqlString(0).Value != null))//We don't want to add variables to a script that doesn't exist, now do we?
                {
                    reader.Close();
                    return false;
                }
            }
            catch (InvalidOperationException)
            {
                return false;
            }
            reader.Close();

            Dictionary<String, long> variablesToRegister = new Dictionary<string, long>();
            foreach (String var in variables)
            {
                variablesToRegister.Add(var, 0);
            }
            this.Commit(0, scriptName, 0, variablesToRegister, false);
            return true;
        }

        public long GetTotalValueByVariable(string variableName)
        {
            SqlCommand command = new SqlCommand("SELECT VALUE FROM " + statsTableName + " WHERE Stat = @variableName", connection);
            command.Parameters.AddWithValue("@variableName", variableName);
            return retrieveTotalValue(command);
        }

        public long GetTotalValueByVariable(string variableName, string scriptName)
        {
            SqlCommand command = new SqlCommand("SELECT VALUE FROM " + statsTableName + " WHERE Script = @scriptName AND Stat = @variableName", connection);
            command.Parameters.AddWithValue("@scriptName", scriptName);
            command.Parameters.AddWithValue("@variableName", variableName);
            return retrieveTotalValue(command);
        }
        public long GetTotalValueByVariable(string variableName, int userId)
        {
            SqlCommand command = new SqlCommand("SELECT VALUE FROM " + statsTableName + " WHERE UID = @userId AND Stat = @variableName", connection);
            command.Parameters.AddWithValue("@userId", userId);
            command.Parameters.AddWithValue("@variableName", variableName);
            return retrieveTotalValue(command);
        }

        public long GetTotalValueByVariable(string variableName, string scriptName, int userId)
        {
            SqlCommand command = new SqlCommand("SELECT VALUE FROM " + statsTableName + " WHERE Script = @scriptName AND UID = @userId AND Stat = @variableName", connection);
            command.Parameters.AddWithValue("@userId", userId);
            command.Parameters.AddWithValue("@variableName", variableName);
            command.Parameters.AddWithValue("@scriptName", scriptName);
            return retrieveTotalValue(command);
        }

        public IList<long> GetEveryValueByVariable(string variableName)
        {
            SqlCommand command = new SqlCommand("SELECT VALUE FROM " + statsTableName + " WHERE Stat = @variableName", connection);
            command.Parameters.AddWithValue("@variableName", variableName);
            return retrieveEveryValue(command);
        }

        public IList<long> GetEveryValueByVariable(string variableName, string scriptName)
        {
            SqlCommand command = new SqlCommand("SELECT VALUE FROM " + statsTableName + " WHERE Script = @scriptName AND Stat = @variableName", connection);
            command.Parameters.AddWithValue("@scriptName", scriptName);
            command.Parameters.AddWithValue("@variableName", variableName);
            return retrieveEveryValue(command);
        }
        public IList<long> GetEveryValueByVariable(string variableName, int userId)
        {
            SqlCommand command = new SqlCommand("SELECT VALUE FROM " + statsTableName + " WHERE UID = @userId AND Stat = @variableName", connection);
            command.Parameters.AddWithValue("@userId", userId);
            command.Parameters.AddWithValue("@variableName", variableName);
            return retrieveEveryValue(command);
        }

        public IList<long> GetEveryValueByVariable(string variableName, string scriptName, int userId)
        {
            SqlCommand command = new SqlCommand("SELECT VALUE FROM " + statsTableName + " WHERE Script = @scriptName AND UID = @userId AND Stat = @variableName", connection);
            command.Parameters.AddWithValue("@userId", userId);
            command.Parameters.AddWithValue("@variableName", variableName);
            command.Parameters.AddWithValue("@scriptName", scriptName);
            return retrieveEveryValue(command);
        }
        
        public IList<String> GetVariablesByScript(string scriptName, int userId)
        {
            SqlCommand command = new SqlCommand("SELECT DISTINCT STAT FROM " + statsTableName + " WHERE Script = @scriptName AND UID = @userId", connection);
            command.Parameters.AddWithValue("@userId", userId);
            command.Parameters.AddWithValue("@scriptName", scriptName);

            IList<String> returns = new List<String>();
            SqlDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    returns.Add(reader.GetSqlString(0).Value);
                }
            }
            catch (InvalidOperationException)
            {
                //swallowed
            }
            reader.Close();
            return returns;
        }

        public IList<String> GetVariablesByScript(string scriptName)
        {
            SqlCommand command = new SqlCommand("SELECT DISTINCT STAT FROM " + statsTableName + " WHERE Script = @scriptName", connection);
            command.Parameters.AddWithValue("@scriptName", scriptName);

            IList<String> returns = new List<String>();
            SqlDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    returns.Add(reader.GetSqlString(0).Value);
                }
            }
            catch (InvalidOperationException)
            {
                //swallowed
            }
            reader.Close();
            return returns;
        }

        public IList<String> GetVariablesByUser(long UID)
        {
            SqlCommand command = new SqlCommand("SELECT DISTINCT STAT FROM " + statsTableName + " WHERE UID = @userID", connection);
            command.Parameters.AddWithValue("@userID", UID);

            IList<String> returns = new List<String>();
            SqlDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    returns.Add(reader.GetSqlString(0).Value);
                }
            }
            catch (InvalidOperationException)
            {
                //swallowed
            }
            reader.Close();
            return returns;
        }

        public long GetTotalTimeBy(string scriptName)
        {
            SqlCommand command = new SqlCommand("SELECT CommitLength FROM " + statsTableName + " WHERE Script = @scriptName", connection);
            command.Parameters.AddWithValue("@scriptName", scriptName);
            return retrieveTotalValue(command);
        }

        public long GetTotalTimeBy(string scriptName, int uid)
        {
            SqlCommand command = new SqlCommand("SELECT CommitLength FROM " + statsTableName + " WHERE Script = @scriptName AND UID = @uid", connection);
            command.Parameters.AddWithValue("@scriptName", scriptName);
            command.Parameters.AddWithValue("@uid", uid);
            return retrieveTotalValue(command);
        }
        public long GetTotalTimeBy(int uid)
        {
            SqlCommand command = new SqlCommand("SELECT CommitLength FROM " + statsTableName + " WHERE UID = @uid", connection);
            command.Parameters.AddWithValue("@uid", uid);
            return retrieveTotalValue(command);
        }

        private IList<long> retrieveEveryValue(SqlCommand command)
        {
            List<long> value = new List<long>();
            SqlDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    value.Add(reader.GetSqlInt64(0).Value);
                }
            }
            catch (InvalidOperationException)
            {
                //swallowed
            }
            reader.Close();
            return value;
        }

        private long retrieveTotalValue(SqlCommand command)
        {
            long value = 0;
            SqlDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    value += reader.GetSqlInt64(0).Value;
                }
            }
            catch (InvalidOperationException)
            {
                //swallowed
            }
            reader.Close();
            return value;
        }

        public void Commit(long uid, string scriptName, long commitLength, Dictionary<String, long> variables, bool adminCheck = true)
        {
            string time = DateTime.UtcNow.ToString();
            if (adminCheck && ((uid <= 0) || (commitLength <= 0)))
            {
                return;//Invalid commit
            }
            foreach (KeyValuePair<String, long> var in variables)
            {
                if ((adminCheck  && CheckIfVariableExists(scriptName, var.Key)) || !adminCheck)//only commit to registered scripts and variables if not an admin
                {
                    SqlCommand command = new SqlCommand("INSERT INTO " + statsTableName + " (UID, Script, CommitTime, CommitLength, Stat, Value) VALUES (@uid,@scriptName,@time,@commitLength,@variableName,@quantity)", connection);
                    command.Parameters.AddWithValue("@uid", uid);
                    command.Parameters.AddWithValue("@scriptName", scriptName);
                    command.Parameters.AddWithValue("@time", time);
                    command.Parameters.AddWithValue("@commitLength", commitLength);
                    command.Parameters.AddWithValue("@variableName", var.Key);
                    command.Parameters.AddWithValue("@quantity", var.Value);
                    command.ExecuteNonQuery();
                }
            }
        }

        public bool CheckIfScriptExists(string scriptName)
        {
            SqlCommand command = new SqlCommand("Select * FROM " + statsTableName + " WHERE Script = @scriptName", connection);
            command.Parameters.AddWithValue("@scriptName", scriptName);
            SqlDataReader reader = command.ExecuteReader();
            bool returnValue = reader.Read();
            reader.Close();
            return returnValue;
        }

        public bool CheckIfUserExists(int uid)
        {
            SqlCommand command = new SqlCommand("Select * FROM " + statsTableName + " WHERE UID = @uid", connection);
            command.Parameters.AddWithValue("@UID", uid);
            SqlDataReader reader = command.ExecuteReader();
            bool returnValue = reader.Read();
            reader.Close();
            return returnValue;
        }

        public bool CheckIfVariableExists(string scriptName, string variableName)
        {
            SqlCommand command = new SqlCommand("Select * FROM " + statsTableName + " WHERE Script = @scriptName AND Stat = @variableName", connection);
            command.Parameters.AddWithValue("@scriptName", scriptName);
            command.Parameters.AddWithValue("@variableName", variableName);
            SqlDataReader reader = command.ExecuteReader();
            bool returnValue = reader.Read();
            reader.Close();
            return returnValue;
        }

        /*async Task TestWriter(string fileName, string variableName, int quantity)
        {

             XmlWriterSettings settings = new XmlWriterSettings();
             settings.Async = true;
             FileStream stream = new FileStream(fileName, FileMode.Open);
             using (XmlWriter writer = XmlWriter.Create(stream, settings))
             {
                 await writer.WriteStartElementAsync("pf", "root", "http://ns");
                 await writer.WriteStartElementAsync(null, "sub", null);
                 await writer.WriteAttributeStringAsync(null, "att", null, "val");
                 await writer.WriteStringAsync("text");
                 await writer.WriteEndElementAsync();
                 await writer.WriteProcessingInstructionAsync("pName", "pValue");
                 await writer.WriteCommentAsync("cValue");
                 await writer.WriteCDataAsync("cdata value");
                 await writer.WriteEndElementAsync();
                 await writer.FlushAsync();
             }
        }*/

        public void Dispose()
        {
            connection.Close();
            connection.Dispose();
        }
    }
}
